/**
 * The Class for operation the database
 */
package com.pxingg.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.pxingg.exception.XException;

/**
 * @author pxingg
 * 
 */
public class XDB
{
    /**
     * The log of this class
     */
    protected static Log log = LogFactory.getLog(XDB.class);

    /**
     * The Database file
     */
    protected static String SQLITE_DATA_FILE = "smarthome.db";

    /**
     * The static sqlite database connection
     */
    protected static Connection conn = null;

    /**
     * 全局公用的数据库对象，使用单对象模式
     */
    protected static XDB db = null;

    /**
     * build a static connection to database
     */
    protected synchronized static void createDatabaseConnection()
    {
        try
        {
            if (XDB.conn == null)
            {
                Class.forName("org.sqlite.JDBC");
                XDB.conn = DriverManager.getConnection("jdbc:sqlite:" + XDB.SQLITE_DATA_FILE);
                XDB.conn.setAutoCommit(false);
                XDB.log.info("success to build connect to database " + XDB.SQLITE_DATA_FILE);
            }
        }
        catch (final Exception e)
        {
            XDB.conn = null;
            XDB.log.error("can not build the connectin to database " + XDB.SQLITE_DATA_FILE);
            XDB.log.error(e);
        }
    }

    /**
     * 获取一个全局的数据库对象
     * @return 一个全局使用的单模式数据库对象
     */
    public static XDB getInstance()
    {
        if (XDB.db == null)
        {
            XDB.db = new XDB();
        }

        return XDB.db;
    }

    /**
     * Just build a connection for use
     */
    private XDB()
    {
        XDB.createDatabaseConnection();
    }

    /**
     * Execute a SQL and get a record
     * 
     * @param sql
     *            a query SQL without parameter
     * @return all record of the SQL
     * @throws XException
     */
    public synchronized String[] queryOneRecord(final String sql) throws XException
    {
        try
        {
            String[] result = null;
            if (XDB.log.isDebugEnabled())
            {
                XDB.log.debug("execute sql: " + sql);
            }
            final Statement stat = XDB.conn.createStatement();
            final ResultSet rs = stat.executeQuery(sql);
            if (rs.next())
            {
                result = new String[rs.getMetaData().getColumnCount()];
                for (int i = 0; i < result.length; i++)
                {
                    result[i] = rs.getString(i + 1);
                }
            }

            rs.close();
            stat.close();

            return result;
        }
        catch (final SQLException e)
        {
            XDB.log.error("failed to excute sql: " + sql);
            XDB.log.error(e);
            throw new XException(e);
        }
    }

    /**
     * Execute a SQL and get all record
     * 
     * @param sql
     *            a query SQL without parameter
     * @return a record of the SQL
     * @throws XException
     */
    public synchronized String[][] queryRecord(final String sql) throws XException
    {
        try
        {
            String[][] result = null;
            final LinkedList<String[]> resultList = new LinkedList<String[]>();
            if(XDB.log.isDebugEnabled())
            {
                XDB.log.debug("execute sql: " + sql);
            }
            final Statement stat = XDB.conn.createStatement();
            final ResultSet rs = stat.executeQuery(sql);
            final int resultColumnCount = rs.getMetaData().getColumnCount();
            while (rs.next())
            {
                final String[] oneRecord = new String[resultColumnCount];
                for (int i = 0; i < resultColumnCount; i++)
                {
                    oneRecord[i] = rs.getString(i + 1);
                }

                resultList.add(oneRecord);
            }

            rs.close();
            stat.close();

            if (resultList.size() > 0)
            {
                result = new String[resultList.size()][];
                final Iterator<String[]> it = resultList.iterator();
                int i = 0;
                while (it.hasNext())
                {
                    result[i++] = it.next();
                }
            }

            return result;
        }
        catch (final SQLException e)
        {
            XDB.log.error("failed to excute sql: " + sql);
            XDB.log.error(e);
            throw new XException(e);
        }
    }

    /**
     * Execute a SQL just like create a table, update a table without parameters.
     * 
     * @param sql
     *            the SQL to execute
     * @return the effective rows of the SQL
     * @throws XException
     */
    public synchronized int update(final String sql) throws XException
    {
        try
        {

            XDB.log.info("execute sql: " + sql);
            final Statement stat = XDB.conn.createStatement();
            stat.executeUpdate(sql);
            XDB.conn.commit();

            final int affectRow = stat.getUpdateCount();
            stat.close();

            return affectRow;
        }
        catch (final SQLException e)
        {
            try
            {
                XDB.conn.rollback();
            }
            catch (final SQLException e1)
            {
                XDB.log.error("failed to rollback sql: " + sql);
            }
            XDB.log.error("failed to excute sql: " + sql);
            XDB.log.error(e);
            throw new XException(e);
        }
    }

    /**
     * Execute a prepare SQL just like insert a record without a record's parameter
     * 
     * @param prepareSQL
     *            a prepare SQL for update
     * @param parameters
     *            a record's parameter
     * @return the effective rows of the SQL
     * @throws XException
     */
    public synchronized int update(final String prepareSQL, final String[] parameters) throws XException
    {
        try
        {
            XDB.log.info("execute sql: " + prepareSQL);
            final PreparedStatement prep = XDB.conn.prepareStatement(prepareSQL);
            for (int i = 0; i < parameters.length; i++)
            {
                prep.setString(i + 1, parameters[i]);
            }
            prep.addBatch();
            prep.executeBatch();
            XDB.conn.commit();

            final int affectRow = prep.getUpdateCount();

            prep.close();

            return affectRow;
        }
        catch (final SQLException e)
        {
            try
            {
                XDB.conn.rollback();
            }
            catch (final SQLException e1)
            {
                XDB.log.error("failed to rollback sql: " + prepareSQL);
            }
            XDB.log.error("failed to excute sql: " + prepareSQL);
            XDB.log.error(e);
            throw new XException(e);
        }
    }

    /**
     * Execute a prepare SQL just like insert many rows record without record's parameter
     * 
     * @param prepareSQL
     *            a prepare SQL for update
     * @param parameters
     *            a record's parameter
     * @return the effective rows of the SQL
     * @throws XException
     */
    public synchronized int update(final String prepareSQL, final String[][] parameters) throws XException
    {
        try
        {

            XDB.log.info("execute sql: " + prepareSQL);
            final PreparedStatement prep = XDB.conn.prepareStatement(prepareSQL);
            for (int i = 0; i < parameters.length; i++)
            {
                for (int j = 0; j < parameters[0].length; j++)
                {
                    prep.setString(j + 1, parameters[i][j]);
                }
                prep.addBatch();
            }
            prep.executeBatch();
            XDB.conn.commit();

            final int affectRow = prep.getUpdateCount();
            prep.close();

            return affectRow;
        }
        catch (final SQLException e)
        {
            try
            {
                XDB.conn.rollback();
            }
            catch (final SQLException e1)
            {
                XDB.log.error("failed to rollback sql: " + prepareSQL);
            }
            XDB.log.error("failed to excute sql: " + prepareSQL);
            XDB.log.error(e);
            throw new XException(e);
        }
    }

    /**
     * 在一个事物中执行一组更新数据库的SQL
     * 
     * @param sqls
     *            一组更新数据库的SQL
     * @return the effective rows of the SQL
     * @throws XException
     */
    public synchronized int update(final String[] sqls) throws XException
    {
        String currentSQL = null;
        Statement stat = null;
        int affectRow = 0;

        final StringBuffer sb = new StringBuffer();
        for (int i = 0; i < sqls.length; i++)
        {
            final String sql = sqls[i];
            if (sql == null)
            {
                // SQL数组中可能存在空的字符串，直接跳过不处理
                continue;
            }

            sb.append(sql);
            sb.append(";");
            if (i != sqls.length - 1)
            {
                sb.append('\n');
            }
        }

        try
        {
            XDB.log.debug("execute batch sql: " + sb.toString());
            stat = XDB.conn.createStatement();
            for(int i = 0; i < sqls.length; i++)
            {
                // 把这些SQL作为一个事物提交
                currentSQL = sqls[i];
                if (currentSQL == null)
                {
                    // SQL数组中可能存在空的字符串，直接跳过不处理
                    continue;
                }
                stat.executeUpdate(currentSQL);
                affectRow += stat.getUpdateCount();
            }

            XDB.conn.commit();

            stat.close();

            return affectRow;
        }
        catch (final SQLException e)
        {
            XDB.log.error("failed to excute sql: " + currentSQL);
            XDB.log.error(e);

            try
            {
                if (stat != null)
                {
                    stat.close();
                }
                XDB.conn.rollback();
            }
            catch (final SQLException e1)
            {
                XDB.log.error("failed to rollback sql: " + sb.toString());
            }
            throw new XException(e);
        }
    }
}
